from flask import Flask, jsonify, request, render_template, session, render_template_string
from contextlib import closing
import sqlite3
import pandas as pd
import pymysql
import os
import re
import joblib
import logging
import datetime

# 创建 Flask 应用
app = Flask(__name__)
app.secret_key = os.urandom(24)
# 加载模型
try:
    decision_tree_model = joblib.load('./best_diabetes_model.pkl')
except Exception as e:
    logging.error(f"加载决策树模型时出错: {e}")
    decision_tree_model = None

# 数据库连接管理
def get_sqlite_connection():
    try:
        return sqlite3.connect('health_records.db')
    except sqlite3.Error as err:
        logging.error(f"SQLite 数据库连接错误: {err}")
        return jsonify({"message": f"SQLite 数据库连接错误: {err}"}), 500

def get_mysql_connection():
    try:
        return pymysql.connect(
            host='localhost',
            port=3306,
            user='root',
            password='123456',
            db='user_db',
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor  # 指定字典游标
        )
    except pymysql.MySQLError as err:
        logging.error(f"MySQL 数据库连接错误: {err}")
        return jsonify({"message": f"MySQL 数据库连接错误: {err}"}), 500

# 日志配置
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)


@app.route('/login', methods=['POST'])
def login():
    username = request.form.get('username')
    password = request.form.get('password')

    if not username:
        return render_template_string("""
                                <script>
                                    alert('登录失败，请检查账号和密码');
                                    window.location.href = "{{ url_for('goin') }}";
                                </script>
                                """)

    conn = get_mysql_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        # 使用 pymysql.cursors.DictCursor 获取字典形式的结果
        from pymysql.cursors import DictCursor

        with conn.cursor(DictCursor) as cursor:
            # 先查询管理员表
            admin_query = "SELECT * FROM admin WHERE username = %s"
            cursor.execute(admin_query, (username,))
            admin_result = cursor.fetchone()

            if admin_result and admin_result['password'] == password:
                session['username'] = username
                session['is_admin'] = True  # 标记为管理员
                return render_template_string("""
                                        <script>
                                            alert('尊敬的管理员，欢迎您的到来');
                                            window.location.href = "{{ url_for('user_management') }}";
                                        </script>
                                        """)

            # 再查询普通用户表
            user_query = "SELECT * FROM user WHERE username = %s"
            cursor.execute(user_query, (username,))
            user_result = cursor.fetchone()

            if user_result and user_result['password'] == password:
                session['username'] = username
                session['is_admin'] = False  # 普通用户
                session['password'] = password  # 存储密码到 session 中

                # 查询用户的 realname, idcard, phone 信息
                cursor.execute('SELECT realname, idcard, phone FROM user WHERE username = %s', (username,))
                user_info = cursor.fetchone()

                if not all([user_info['realname'], user_info['idcard'], user_info['phone']]):
                    return render_template_string("""
                                                <script>
                                                    alert('请完善个人信息');
                                                    window.location.href = "{{ url_for('inreviseuser') }}";
                                                </script>
                                                """)
                else:
                    return render_template_string("""
                                            <script>
                                                alert('登录成功');
                                                window.location.href = "{{ url_for('home') }}";
                                            </script>
                                            """)
            else:
                return render_template_string("""
                                        <script>
                                            alert('登录失败，请检查账号和密码');
                                            window.location.href = "{{ url_for('goin') }}";
                                        </script>
                                        """)
    except pymysql.MySQLError as err:
        logging.error(f"数据库查询错误: {err}")
        return jsonify({"message": "数据库错误，请重试"}), 500
    finally:
        conn.close()
# 用户注册
@app.route('/register', methods=["POST"])
def register():
    name = request.form.get('username')
    password = request.form.get('password')

    # 前端验证
    if not name or not password:
        return render_template_string("""
                                    <script>
                                        alert('请输入有效的注册信息');
                                        window.location.href = "{{ url_for('goin') }}";
                                    </script>
                                    """)

    # 后端验证账号格式
    if not re.match(r'^\d{8}$', name):
        return render_template_string("""
                                    <script>
                                        alert('账号必须为 8 位整数');
                                        window.location.href = "{{ url_for('goin') }}";
                                    </script>
                                    """)

    # 后端验证密码格式
    if len(password) == 0:
        return render_template_string("""
                                    <script>
                                        alert('请输入密码');
                                        window.location.href = "{{ url_for('goin') }}";
                                    </script>
                                    """)

    conn = get_mysql_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        with conn.cursor() as cursor:
            query = "SELECT * FROM user WHERE username = %s"
            cursor.execute(query, (name,))
            result = cursor.fetchall()

            if len(result) > 0:
                return render_template_string("""
                                        <script>
                                            alert('账号已存在');
                                            window.location.href = "{{ url_for('goin') }}";
                                        </script>
                                        """)
            else:
                insert_query = "INSERT INTO user (username, password) VALUES (%s, %s)"
                cursor.execute(insert_query, (name, password))
                conn.commit()
                return render_template_string("""
                                        <script>
                                            alert('注册成功');
                                            window.location.href = "{{ url_for('goin') }}";
                                        </script>
                                        """)
    except pymysql.MySQLError as err:
        logging.error(f"数据库插入错误: {err}")
        conn.rollback()
        return render_template_string("""
                                    <script>
                                        alert('注册失败，请重试');
                                        window.location.href = "{{ url_for('goin') }}";
                                    </script>
                                    """)

@app.route('/reviseuser', methods=["POST"])
def reviseuser():
    if 'username' not in session:
        return jsonify({"message": "用户未登录"}), 401

    name = request.form.get('realname')
    idcard = request.form.get('idcard')
    phone = request.form.get('phone')
    old_password = request.form.get('oldpassword')
    newpassword = request.form.get('newpassword')

    # 检查是否有任何更新内容
    has_info_update = any([name, idcard, phone])
    has_password_update = bool(newpassword)

    if not has_info_update and not has_password_update:
        return render_template_string("""
                                        <script>
                                            alert('请输入要更新的信息或密码');
                                            window.location.href = "{{ url_for('inreviseuser') }}";
                                        </script>
                                        """)

    # 如果有信息更新，验证必填字段
    if has_info_update:
        if not all([name, idcard, phone]):
            missing_fields = []
            if not name:
                missing_fields.append("姓名")
            if not idcard:
                missing_fields.append("身份证号")
            if not phone:
                missing_fields.append("手机号")
            return render_template_string("""
                                            <script>
                                                alert('请输入完整的身份信息');
                                                window.location.href = "{{ url_for('inreviseuser') }}";
                                            </script>
                                            """)

        # 验证身份证格式
        if not re.match(r'^\d{17}[\dXx]$', idcard):
            return render_template_string("""
                                            <script>
                                                alert('请输入有效的身份证信息');
                                                window.location.href = "{{ url_for('inreviseuser') }}";
                                            </script>
                                            """)

    # 验证新密码长度
    if newpassword and len(newpassword) < 3:
        return render_template_string("""
                                        <script>
                                            alert('新密码长度至少为3位');
                                            window.location.href = "{{ url_for('inreviseuser') }}";
                                        </script>
                                        """)

    password = None  # 默认不更新密码

    # 如果有新密码且与原密码不同，更新 password
    if newpassword:
        # 检查旧密码是否正确
        if old_password == '' or old_password != session.get('password'):
            return jsonify({"message": "请输入正确的旧密码"}), 400
        password = newpassword

    conn = get_mysql_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        with conn.cursor() as cursor:
            # 获取用户旧信息
            query = 'SELECT realname, idcard, phone FROM user WHERE username = %s'
            cursor.execute(query, (session['username'],))
            old_user_data = cursor.fetchone()

            old_name = old_user_data['realname']
            old_idcard = old_user_data['idcard']
            old_phone = old_user_data['phone']

            # 如果更新了身份证号，检查是否已被使用（排除当前用户）
            if has_info_update and idcard != old_idcard:
                query = 'SELECT username FROM user WHERE idcard = %s AND username != %s'
                cursor.execute(query, (idcard, session['username']))
                if cursor.fetchone():
                    return render_template_string("""
                                                <script>
                                                    alert('该身份证号已被使用');
                                                    window.location.href = "{{ url_for('inreviseuser') }}";
                                                </script>
                                                """)

            # 构建动态的 SQL 更新语句
            update_fields = []
            update_values = []

            # 只有当提供了信息更新时才更新这些字段
            if has_info_update:
                update_fields.append("realname = %s")
                update_values.append(name)
                update_fields.append("idcard = %s")
                update_values.append(idcard)
                update_fields.append("phone = %s")
                update_values.append(phone)

            # 如果提供了新密码，更新密码字段
            if password:
                update_fields.append("password = %s")
                update_values.append(password)

            # 如果没有要更新的字段
            if not update_fields:
                return render_template_string("""
                                            <script>
                                                alert('没有要更新的内容');
                                                window.location.href = "{{ url_for('inreviseuser') }}";
                                            </script>
                                            """)

            update_values.append(session['username'])

            query = "UPDATE user SET " + ", ".join(update_fields) + " WHERE username = %s"
            logger.info(f"执行的 SQL 语句: {query}, 参数: {update_values}")
            cursor.execute(query, tuple(update_values))
            conn.commit()

            # 更新 session 中的密码（如果已更改）
            if password:
                session['password'] = password

            # 检查信息是否有变动
            info_changed = has_info_update and (name != old_name or idcard != old_idcard or phone != old_phone)

            if password or info_changed:
                return render_template_string("""
                        <script>
                            alert('修改成功');
                            window.location.href = "{{ url_for('home') }}";
                        </script>
                        """)
            else:
                return render_template_string("""
                                    <script>
                                        alert('请输入有效的新密码或信息');
                                        window.location.href = "{{ url_for('inreviseuser') }}";
                                    </script>
                                    """)
    except pymysql.MySQLError as err:
        logging.error(f"数据库插入错误: {err}")
        conn.rollback()
        return jsonify({"message": "数据库错误: 请重试"}), 500
    except Exception as e:
        logging.error(f"发生未知错误: {e}")
        return jsonify({"message": "发生未知错误，请重试"}), 500

@app.route('/user', methods=["GET", "POST"])
def user():
    if 'username' not in session:
        return jsonify({"message": "用户未登录"}), 401

    conn = get_mysql_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        with conn.cursor() as cursor:
            # 根据 session 中的 username 查询用户信息
            query = 'SELECT username, realname, idcard, phone FROM user WHERE username = %s'
            cursor.execute(query, (session['username'],))
            user_data = cursor.fetchone()

            if user_data:
                user = {
                    'username': user_data['username'],
                    'realname': user_data['realname'],
                    'idcard': user_data['idcard'],
                    'phone': user_data['phone']
                }
                session['user'] = user
            else:
                session['user'] = {}

            return render_template('user.html', user=session.get('user', {}))
    except Exception as e:
        logging.error(f"发生错误: {e}")
        return "发生错误，请稍后重试。"
    finally:
        conn.close()

# 糖尿病预测
@app.route('/predict', methods=['POST'])
def predict():
    logger.info("进入预测接口")

    # ---------------------- 基础请求校验 ----------------------
    logger.info(f"请求方法: {request.method}")
    logger.info(f"请求内容类型: {request.content_type}")
    logger.info(f"原始表单数据: {request.form}")

    # 1. 检查用户登录状态（示例使用session，实际需根据认证方式调整）
    if 'username' not in session:
        logger.warning("用户未登录")
        return jsonify({
            "code": 401,
            "message": "用户未登录",
            "data": None
        }), 401

    # ---------------------- MySQL查询用户身份证号 ----------------------
    mysql_conn = get_mysql_connection()
    if isinstance(mysql_conn, tuple):  # 连接失败时返回错误元组
        return jsonify({
            "code": 500,
            "message": mysql_conn[0],
            "data": None
        }), mysql_conn[1]

    try:
        with mysql_conn.cursor() as cursor:
            query = "SELECT idcard FROM user WHERE username = %s"
            cursor.execute(query, (session['username'],))
            user_info = cursor.fetchone()
            idcard = user_info['idcard'] if user_info else ""

        logger.info(f"查询到用户IDCard: {idcard}")

    except pymysql.MySQLError as e:
        logger.error(f"MySQL数据库错误: {str(e)}")
        return jsonify({
            "code": 500,
            "message": "数据库查询失败",
            "data": None
        }), 500
    except Exception as e:
        logger.error(f"查询用户信息异常: {str(e)}")
        return jsonify({
            "code": 500,
            "message": "获取用户信息失败",
            "data": None
        }), 500
    finally:
        try:
            mysql_conn.ping()  # 检查连接是否有效
            mysql_conn.close()
            logger.info("关闭MySQL连接")
        except pymysql.Error as e:
            logger.error(f"关闭MySQL连接失败: {str(e)}")

    # ---------------------- 数据校验与预处理 ----------------------
    required_fields = {
        "gender": "性别",
        "age": "年龄",
        "hypertension": "高血压",
        "heart_disease": "心脏病",
        "smoking_history": "吸烟史",
        "bmi": "BMI",
        "HbA1c_level": "糖化血红蛋白",
        "blood_glucose_level": "血糖水平"
    }

    # 校验必填字段
    for field_key, field_name in required_fields.items():
        value = request.form.get(field_key)
        if not value:  # 处理空值/缺失值
            logger.warning(f"缺失字段: {field_name}")
            return jsonify({
                "code": 400,
                "message": f"缺少必要字段: {field_name}",
                "data": None
            }), 400

    # 数据类型转换（带异常处理）
    try:
        data = {
            "gender": int(request.form["gender"]),
            "age": float(request.form["age"]),
            "hypertension": int(request.form["hypertension"]),
            "heart_disease": int(request.form["heart_disease"]),
            "smoking_history": int(request.form["smoking_history"]),
            "bmi": float(request.form["bmi"]),
            "HbA1c_level": float(request.form["HbA1c_level"]),
            "blood_glucose_level": float(request.form["blood_glucose_level"])
        }
    except ValueError as e:
        logger.error(f"数据类型转换失败: {str(e)}")
        return jsonify({
            "code": 400,
            "message": "数据格式错误，请检查输入类型",
            "data": None
        }), 400

    # ---------------------- 模型预测 ----------------------
    if not decision_tree_model:  # 确保模型已加载
        logger.error("模型未加载")
        return jsonify({"code": 500, "message": "模型未加载", "data": None}), 500

    try:
        features = list(data.values())
        # 获取原始概率（无需调用predict()）
        probability = decision_tree_model.predict_proba([features])[0][1] * 100  # 转换为百分比
        probability = round(probability, 2)  # 保留两位小数

        # 根据阈值（20%）判断结果
        diabetes = 1 if probability >= 20 else 0
        logger.info(f"预测概率: {probability}%, 阈值判断结果: {diabetes}")

    except Exception as e:
        logger.error(f"模型预测失败: {str(e)}")
        return jsonify({"code": 500, "message": "预测失败，请重试", "data": None}), 500

    # ---------------------- SQLite存储结果 ----------------------
    sqlite_conn = get_sqlite_connection()
    if isinstance(sqlite_conn, tuple):
        return jsonify({
            "code": 500,
            "message": sqlite_conn[0],
            "data": None
        }), sqlite_conn[1]

    try:
        with sqlite_conn as conn:  # 使用上下文管理器自动提交/回滚
            cursor = conn.cursor()

            # 插入数据
            insert_sql = '''
            INSERT INTO records (
                idcard, timestamp, gender, age, hypertension, 
                heart_disease, smoking_history, bmi, HbA1c_level, 
                blood_glucose_level, diabetes
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            '''
            timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cursor.execute(insert_sql, (
                idcard,
                timestamp,
                data["gender"],
                data["age"],
                data["hypertension"],
                data["heart_disease"],
                data["smoking_history"],
                data["bmi"],
                data["HbA1c_level"],
                data["blood_glucose_level"],
                diabetes
            ))
            logger.info("数据存储到SQLite成功")

    except sqlite3.Error as e:
        logger.error(f"SQLite数据库错误: {str(e)}")
        sqlite_conn.rollback()
        return jsonify({
            "code": 500,
            "message": "存储预测结果失败",
            "data": None
        }), 500
    finally:
        try:
            sqlite_conn.close()
            logger.info("关闭SQLite连接")
        except sqlite3.Error as e:
            logger.error(f"关闭SQLite连接失败: {str(e)}")

    # ---------------------- 返回结果 ----------------------
    return render_template('result.html', **{
        "diabetes": diabetes,  # 直接返回数值
        "probability": probability,
        **data
    })

# 获取历史记录
def get_history_records(page=1, limit=10, date=None, idcard=None):
    conn = get_sqlite_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    records = []
    total_records = 0
    try:
        with closing(conn.cursor()) as c:  # 使用closing包装游标
            query = "SELECT * FROM records"
            params = []

            if date:
                query += " WHERE DATE(timestamp) = ?"
                params.append(date)
            if idcard:
                if 'WHERE' in query:
                    query += " AND idcard = ?"
                else:
                    query += " WHERE idcard = ?"
                params.append(idcard)

            # 获取总记录数
            total_records = len(c.execute(query, tuple(params)).fetchall())

            # 添加排序和分页
            query += " ORDER BY timestamp DESC LIMIT ? OFFSET ?"
            params.append(limit)
            params.append((page - 1) * limit)  # 计算偏移量

            records = c.execute(query, tuple(params)).fetchall()

        mysql_conn = get_mysql_connection()
        if isinstance(mysql_conn, tuple):  # 检查是否返回错误信息
            return mysql_conn

        try:
            with mysql_conn.cursor() as mysql_cursor:
                new_records = []
                for record in records:
                    idcard = record[1]  # 假设身份证号在记录的第二个位置
                    mysql_cursor.execute("SELECT realname FROM user WHERE idcard = %s", (idcard,))
                    result = mysql_cursor.fetchone()

                    if result:
                        realname = result['realname']  # 假设查询结果是字典形式
                    else:
                        realname = "未知"
                    new_record = list(record)
                    new_record.insert(1, realname)  # 在记录中插入用户姓名
                    new_records.append(new_record)

            return new_records, total_records
        except pymysql.MySQLError as e:
            logging.error(f"MySQL查询错误: {e}")
            return jsonify({"message": "MySQL查询错误，请重试"}), 500
        finally:
            mysql_conn.close()
    except sqlite3.Error as e:
        logging.error(f"SQLite查询错误: {e}")
        return jsonify({"message": "SQLite查询错误，请重试"}), 500
    finally:
        conn.close()

@app.route('/history')
def history():
    return render_template('history.html')

@app.route('/history_data')
def history_data():
    date = request.args.get('date', None)
    page = request.args.get('page', 1, type=int)
    records, total_records = get_history_records(page=page, date=date)

    if isinstance(records, tuple):  # 检查是否返回错误信息
        return records

    # 处理records中的bytes类型数据
    def convert_bytes(obj):
        if isinstance(obj, bytes):
            try:
                return obj.decode('utf-8')  # 尝试使用UTF-8解码
            except UnicodeDecodeError:
                return obj.decode('latin-1')  # 或其他合适的编码
        elif isinstance(obj, list):
            return [convert_bytes(item) for item in obj]
        elif isinstance(obj, dict):
            return {k: convert_bytes(v) for k, v in obj.items()}
        return obj

    processed_records = convert_bytes(records)

    return jsonify({'records': processed_records, 'total_records': total_records})

# 获取患者数据
@app.route('/patient_data')
def patient_data():
    idcard = request.args.get('idcard')
    if idcard:
        records, total_records = get_history_records(date=None, idcard=idcard)
        if isinstance(records, tuple):  # 检查是否返回错误信息
            return records
        return jsonify({'records': records})
    return jsonify({'records': []})

# 获取糖尿病统计数据
def get_diabetes_statistics():
    conn = get_sqlite_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    total_diabetic = 0
    total_normal = 0

    try:
        # 显式创建游标
        c = conn.cursor()

        # 查询糖尿病患者数量
        c.execute("SELECT COUNT(*) FROM records WHERE diabetes = 1")
        total_diabetic = c.fetchone()[0]

        # 查询正常患者数量
        c.execute("SELECT COUNT(*) FROM records WHERE diabetes = 0")
        total_normal = c.fetchone()[0]

        return total_diabetic, total_normal

    except sqlite3.Error as e:
        logging.error(f"SQLite查询错误: {e}")
        return jsonify({"message": "SQLite查询错误，请重试"}), 500
    finally:
        # 显式关闭游标和连接
        if conn:
            conn.close()

@app.route('/diabetes_stats')
def diabetes_stats():
    total_diabetic, total_normal = get_diabetes_statistics()
    if isinstance(total_diabetic, tuple):  # 检查是否返回错误信息
        return total_diabetic
    return jsonify({'diabetic': total_diabetic, 'normal': total_normal})

# 获取糖尿病患者各项指标的均值
def get_diabetes_means():
    conn = get_sqlite_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        # 显式创建游标
        c = conn.cursor()

        # 查询正常患者的各项指标均值
        c.execute("""  
            SELECT AVG(gender), AVG(blood_glucose_level), AVG(HbA1c_level), AVG(hypertension),   
                   AVG(heart_disease), AVG(bmi), AVG(age), AVG(smoking_history)   
            FROM records WHERE diabetes = 0  
        """)
        normal_means = c.fetchone()

        # 查询糖尿病患者的各项指标均值
        c.execute("""  
            SELECT AVG(gender), AVG(blood_glucose_level), AVG(HbA1c_level), AVG(hypertension),   
                   AVG(heart_disease), AVG(bmi), AVG(age), AVG(smoking_history)   
            FROM records WHERE diabetes = 1  
        """)
        diabetic_means = c.fetchone()

        return {
            'normal': normal_means,
            'diabetic': diabetic_means
        }
    except sqlite3.Error as e:
        logging.error(f"SQLite查询错误: {e}")
        return jsonify({"message": "SQLite查询错误，请重试"}), 500
    finally:
        # 显式关闭游标和连接
        if conn:
            conn.close()

@app.route('/diabetes_means')
def diabetes_means():
    means = get_diabetes_means()
    if isinstance(means, tuple):  # 检查是否返回错误信息
        return means
    return jsonify(means)

# 获取数据库数据
def get_xxdata_from_db():
    conn = get_sqlite_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        query = "SELECT gender, age, hypertension, heart_disease, smoking_history, bmi, HbA1c_level, blood_glucose_level, diabetes FROM records"
        df = pd.read_sql_query(query, conn)
        return df
    except Exception as e:
        logging.error(f"读取数据库数据错误: {e}")
        return jsonify({"message": "读取数据库数据错误，请重试"}), 500
    finally:
        conn.close()

@app.route('/api/xxdata')
def data():
    df = get_xxdata_from_db()
    if isinstance(df, tuple):  # 检查是否返回错误信息
        return df

    # 处理DataFrame中的特殊类型
    for col in df.columns:
        if df[col].dtype == 'object':  # 检查是否为对象类型
            df[col] = df[col].apply(lambda x: x.decode('utf-8') if isinstance(x, bytes) else x)

    data = df.to_dict(orient='records')
    return jsonify(data)


@app.route('/user_management', methods=["GET"])
def user_management():
    if 'username' not in session:
        return jsonify({"message": "用户未登录"}), 401

    # 检查是否为管理员（新增）
    if session.get('is_admin') != True:
        return jsonify({"message": "无权限访问"}), 403

    page = request.args.get('page', 1, type=int)
    per_page = 10

    conn = get_mysql_connection()
    if isinstance(conn, tuple):
        return conn

    try:
        with conn.cursor() as cursor:
            # 先查询用户总数（调整查询顺序）
            cursor.execute('SELECT COUNT(*) as total FROM user')
            total = cursor.fetchone()['total']
            total_pages = (total + per_page - 1) // per_page if total > 0 else 1

            # 页码有效性验证（新增）
            if page < 1:
                page = 1
            elif page > total_pages:
                page = total_pages

            offset = (page - 1) * per_page

            # 查询指定页的用户信息（移除password字段）
            query = 'SELECT username, realname, idcard, phone FROM user LIMIT %s OFFSET %s'
            cursor.execute(query, (per_page, offset))
            users = cursor.fetchall()

        return render_template('usermanagement.html',
                               users=users,
                               page=page,
                               total_pages=total_pages,
                               total=total)
    except Exception as e:
        logging.error(f"发生错误: {e}")
        return "发生错误，请稍后重试。"
    finally:
        conn.close()

@app.route('/index')
def index():
    if 'username' not in session:
        return jsonify({"message": "用户未登录"}), 401

    conn = get_mysql_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        with conn.cursor() as cursor:
            cursor.execute('SELECT idcard FROM user WHERE username = %s', (session['username'],))
            user_info = cursor.fetchone()
            idcard = user_info['idcard'] if user_info else ''
        return render_template('index.html', idcard=idcard)
    except pymysql.MySQLError as err:
        logging.error(f"数据库查询错误: {err}")
        return jsonify({"message": "数据库错误，请重试"}), 500
    finally:
        conn.close()

@app.route('/add_user', methods=['POST'])
def add_user():
    # 获取表单数据
    username = request.form.get('username')
    realname = request.form.get('realname')
    idcard = request.form.get('idcard')
    password = request.form.get('password')
    phone = request.form.get('phone')

    # 检查身份证号是否已存在
    conn = get_mysql_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        with conn.cursor() as cursor:
            # 检查身份证号是否已存在
            query = "SELECT * FROM user WHERE idcard = %s"
            cursor.execute(query, (idcard,))
            existing_user = cursor.fetchone()

            if existing_user:
                return render_template_string("""
                                                            <script>
                                                                alert('用户已存在');
                                                                window.location.href = "{{ url_for('user_management') }}";
                                                            </script>
                                #                             """)

            # 创建新用户
            query = "INSERT INTO user (username, realname, idcard, password, phone) VALUES (%s, %s, %s, %s, %s)"
            cursor.execute(query, (username, realname, idcard, password, phone))
            conn.commit()

            return render_template_string("""
                                                            <script>
                                                                alert('用户添加成功');
                                                                window.location.href = "{{ url_for('user_management') }}";
                                                            </script>
                                #                             """)
    except pymysql.MySQLError as err:
        conn.rollback()
        logging.error(f'添加用户失败：{str(err)}')
        return render_template_string("""
                                                                    <script>
                                                                        alert('用户添加失败');
                                                                        window.location.href = "{{ url_for('user_management') }}";
                                                                    </script>
                                        #                             """)
    finally:
        conn.close()

@app.route('/delete_user/<username>', methods=["GET"])
def delete_user(username):
    if 'username' not in session:
        return jsonify({"message": "用户未登录"}), 401

    conn = get_mysql_connection()
    if isinstance(conn, tuple):  # 检查是否返回错误信息
        return conn

    try:
        with conn.cursor() as cursor:
            # 删除指定用户名的用户信息
            query = 'DELETE FROM user WHERE username = %s'
            cursor.execute(query, (username,))
            conn.commit()
        return render_template_string("""
                                                <script>
                                                    alert('删除成功');
                                                    window.location.href = "{{ url_for('user_management') }}";
                                                </script>
                    #                             """)
    except Exception as e:
        logging.error(f"删除用户时发生错误: {str(e)}")
        return jsonify({"message": f"删除用户时发生错误: {str(e)}，请稍后重试。"}), 500
    finally:
        conn.close()

# 其他路由
@app.route('/')
def goin():
    return render_template('login.html')

@app.route('/home')
def home():
    return render_template('home.html')

@app.route('/reviseuser')
def inreviseuser():
    return render_template('reviseuser.html')

@app.route('/logout')
def logout():
    session.clear()  # 清除会话信息
    return render_template_string("""
                                            <script>
                                                alert('退出成功，祝您有一个健康的身体！');
                                                window.location.href = "{{ url_for('goin') }}";
                                            </script>
                #                             """)

@app.route('/dashboard')
def dashboard():
    return render_template('dashboard.html')


@app.route('/api/auto-load-csv', methods=['GET'])
def auto_load_csv():
    try:
        # 指定要自动加载的CSV文件路径
        csv_file_path = 'diabetes_filtered.csv'  # 替换为实际文件路径

        # 检查文件是否存在
        if not os.path.exists(csv_file_path):
            return jsonify({'success': False, 'error': f'预设CSV文件不存在: {csv_file_path}'}), 404

        # 读取CSV文件
        df = pd.read_csv(csv_file_path)

        # 数据预处理
        # 确保必要的列存在
        required_columns = ['age', 'diabetes', 'hypertension', 'heart_disease', 'bmi', 'HbA1c_level']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            return jsonify({'success': False, 'error': f'CSV文件缺少必要的列: {", ".join(missing_columns)}'}), 400

        # 数据清洗和类型转换
        df['age'] = pd.to_numeric(df['age'], errors='coerce').fillna(0)
        df['diabetes'] = df['diabetes'].astype(int)
        df['hypertension'] = df['hypertension'].astype(int)
        df['heart_disease'] = df['heart_disease'].astype(int)
        df['bmi'] = pd.to_numeric(df['bmi'], errors='coerce').fillna(0)
        df['HbA1c_level'] = pd.to_numeric(df['HbA1c_level'], errors='coerce').fillna(0)

        # 过滤无效数据
        df = df[df['age'] >= 0]  # 确保年龄非负

        # 准备返回数据
        return jsonify({
            'success': True,
            'message': '文件加载成功',
            'data': {
                'total_samples': len(df),
                'diabetes_count': len(df[df['diabetes'] == 1]),
                'non_diabetes_count': len(df[df['diabetes'] == 0]),
                'diabetes_percent': len(df[df['diabetes'] == 1]) / len(df) * 100,
                'non_diabetes_percent': len(df[df['diabetes'] == 0]) / len(df) * 100,
                'avg_age': df['age'].mean(),
                'age_distribution': calculate_age_distribution(df),
                'risk_factors': calculate_risk_factors(df),
                'raw_data': df.to_dict('records')
            }
        }), 200

    except Exception as e:
        # 打印详细的错误堆栈信息到服务器日志
        import traceback
        traceback.print_exc()

        return jsonify({'success': False, 'error': f'加载文件时出错: {str(e)}'}), 500


# 计算年龄分布
def calculate_age_distribution(df):
    # 创建年龄区间（0-80岁分为8个区间）
    age_bins = [0, 10, 20, 30, 40, 50, 60, 70, 80]
    age_labels = ['0-10', '11-20', '21-30', '31-40', '41-50', '51-60', '61-70', '71-80']

    # 使用pd.cut进行分组，并统计每个区间的数量
    age_distribution = pd.cut(df['age'], bins=age_bins, labels=age_labels, right=False).value_counts()

    # 使用reindex确保所有区间都存在，缺失的区间会被填充为0
    age_distribution = age_distribution.reindex(age_labels, fill_value=0)

    # 将结果转换为列表
    result = age_distribution.tolist()

    return result

# 计算风险因素
def calculate_risk_factors(df):
    # 分离糖尿病和非糖尿病患者
    diabetes_df = df[df['diabetes'] == 1]
    non_diabetes_df = df[df['diabetes'] == 0]

    # 计算高血压比例
    hypertension_in_diabetes = len(diabetes_df[diabetes_df['hypertension'] == 1]) / len(diabetes_df) * 100
    hypertension_in_non_diabetes = len(non_diabetes_df[non_diabetes_df['hypertension'] == 1]) / len(
        non_diabetes_df) * 100

    # 计算心脏病比例
    heart_disease_in_diabetes = len(diabetes_df[diabetes_df['heart_disease'] == 1]) / len(diabetes_df) * 100
    heart_disease_in_non_diabetes = len(non_diabetes_df[non_diabetes_df['heart_disease'] == 1]) / len(
        non_diabetes_df) * 100

    # 计算高BMI比例 (BMI > 25)
    bmi_high_in_diabetes = len(diabetes_df[diabetes_df['bmi'] > 25]) / len(diabetes_df) * 100
    bmi_high_in_non_diabetes = len(non_diabetes_df[non_diabetes_df['bmi'] > 25]) / len(non_diabetes_df) * 100

    # 计算高HbA1c比例 (HbA1c > 5.7)
    hba1c_high_in_diabetes = len(diabetes_df[diabetes_df['HbA1c_level'] > 5.7]) / len(diabetes_df) * 100
    hba1c_high_in_non_diabetes = len(non_diabetes_df[non_diabetes_df['HbA1c_level'] > 5.7]) / len(non_diabetes_df) * 100

    return {
        'hypertension': {
            'name': '高血压比例',
            'diabetes': hypertension_in_diabetes,
            'non_diabetes': hypertension_in_non_diabetes,
            'risk_ratio': hypertension_in_diabetes / (hypertension_in_non_diabetes or 1)
        },
        'heart_disease': {
            'name': '心脏病比例',
            'diabetes': heart_disease_in_diabetes,
            'non_diabetes': heart_disease_in_non_diabetes,
            'risk_ratio': heart_disease_in_diabetes / (heart_disease_in_non_diabetes or 1)
        },
        'bmi_high': {
            'name': '高BMI比例',
            'diabetes': bmi_high_in_diabetes,
            'non_diabetes': bmi_high_in_non_diabetes,
            'risk_ratio': bmi_high_in_diabetes / (bmi_high_in_non_diabetes or 1)
        },
        'hba1c_high': {
            'name': '高HbA1c比例',
            'diabetes': hba1c_high_in_diabetes,
            'non_diabetes': hba1c_high_in_non_diabetes,
            'risk_ratio': hba1c_high_in_diabetes / (hba1c_high_in_non_diabetes or 1)
        }
    }

if __name__ == '__main__':
    app.run(debug=True)